const express = require('express')
const c = require('../connection')
const router = express.Router()
/* router.get('/search', (req, res, next) => {
    var obj = req.query == 'undefined' ? '' : obj
    if (obj == undefined) {
        c.query('select * from cs_member', [obj], (err, r) => {
            if (err) {
                return next(err)
            }
            console.log(r);
            console.log(err);
            if (r.length === 0) {
                res.send({
                    code: 401,
                    msg: '该员工不存在'
                })
            } else {
                res.send({
                    code: 200,
                    msg: '查询成功',
                    data: r
                })
            }
        })
    }
    c.query(`select * from cs_member where lid like ? or uname like ? or tel like ? `, [`%${obj}%`], (err, r) => {
        if (err) {
            return next(err)
        }
        res.send({
            code: 200,
            msg: '查询成功',
            data: r
        })
    })
}) */


//  员工分组和模糊查询接口
// url:http://127.0.0.1:3000/member?list
router.get("/list", (req, res) => {
    // 页数给默认值 输入的值或者为1
    var pageNum = req.query.pageNum * 1 || 1;
    //  每页显示数量给默认值 输入的值或者为5
    var pageCount = req.query.pageCount * 1 || 5;
    //  分页查询的开始页数
    var start = (pageNum - 1) * pageCount;
    //  输入的名字给默认值空或者输入的值
    var uname = req.query.uname || '';
    //  最低工资给默认值 输入的值或者为0
    var minSalary = req.query.min * 1 || 0;
    //  最高工资给默认值 输入的值或者为20000
    var maxSalary = req.query.max * 1 || 20000;
    //  打桩 看能否获得上述的默认值
    console.log(minSalary, maxSalary, pageCount, pageNum, uname);
    //  SQL语句查询出工资在?-?之间的员工并进行模糊查询
    c.query(`select count(lid) as count from cs_member,cs_department where deptid=did and salary between ? and ? and uname like ? `, [minSalary, maxSalary, `%${uname}%`], (err, r) => {
        if (err) throw err;
        //  如果返回的数据长度>0则再次进行查询
        if (r.length > 0) {
            //  声明一个变量,保存数据数组中的总数
            var totalCount = r[0].count;
            //  执行SQL语句 查询出当员工姓名是? 并且工资在?-?之间 并进行分组
            c.query(`select * from cs_member,cs_department where deptid=did and uname like ? and salary between ? and ? limit ?,? `, [`%${uname}%`, minSalary, maxSalary, start * 1, pageCount * 1], (err, r) => {
                if (err) throw err;
                if (r.length > 0) {
                    res.send({
                        code: 200,
                        proList: r,
                        totalCount: totalCount,
                    })
                } else {
                    res.send({
                        code: 401,
                        msg: '没有查询到符合条件的员工'
                    })
                }
            })
        }
    })
});

//  员工查询接口
router.get('/', (req, res, next) => {
    var obj = req.query
    console.log(obj);
    c.query('select * from cs_member where uname like ?', [`%${obj.uname}%`], (err, r) => {
        if (err) {
            return next(err)
        }
        if (r.length > 0) {
            res.send({
                code: 200,
                msg: '查询成功',
                data: r
            })
        } else {
            res.send({
                code: 401,
                msg: '查询失败'
            })
        }
    })
})

//  员工信息修改接口
router.post('/update', (req, res) => {
    var obj = req.body
    console.log(obj);
    c.query(`update cs_member set ? where lid=?`, [obj, obj.lid], (err, r) => {
        if (err) {
            throw err
        }
        if (r.changedRows === 0) {
            res.send({
                code: 401,
                msg: '修改失败'
            })
        } else {
            res.send({
                code: 200,
                msg: '修改成功',
                data: r
            })
        }
    })
})

//  员工信息删除接口
router.post('/delete', (req, res, next) => {
    var obj = req.body
    console.log(obj);
    c.query(`delete from cs_member where lid=?`, [obj.lid], (err, r) => {
        if (err) {
            throw err
        }
        if (r.affectedRows === 1) {
            res.send({
                code: 200,
                msg: '删除成功',
                data: r
            })
        } else {
            res.send({
                code: 401,
                msg: '删除失败',
                data: r
            })
        }
    })
})

//  员工新增接口
router.post('/add', (req, res, next) => {
    const obj = req.body
    c.query('insert into cs_member set?', [obj], (err, r) => {
        if (err) {
            return next(err)
        }
        console.log(r);
        res.send({
            code: 200,
            msg: '员工信息添加成功'
        })
    })
})

/* router.get('/kb',(req,res,next)=>{
    var obj=req.query
    c.query(`select lid,uname,sex,birthday,address,tel,dname from cs_member,cs_department where deptid=did and dname like ?`,[`%${obj.dname}%`],(err,r)=>{
        if(err){
            return next(err)
        }
        res.send({
            code:200,
            msg:'查询成功',
            data:r
        })
    })
}) */
module.exports = router